﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VideoClassLibrary.Model;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace VideoClassLibrary.BusinessLogicLayer
{
    public class VideoCommentModule
    {
        #region  成员方法

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID, int CourseListId, int CommentUserId, string CommentUserName, string CommentContent, DateTime CreatedTime)
        {
            Database db = VDataBase.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from V_VideoComment where ID=@ID and CourseListId=@CourseListId and CommentUserId=@CommentUserId and CommentUserName=@CommentUserName and CommentContent=@CommentContent and CreatedTime=@CreatedTime ");
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, ID);
            db.AddInParameter(dbCommand, "CourseListId", DbType.Int32, CourseListId);
            db.AddInParameter(dbCommand, "CommentUserId", DbType.Int32, CommentUserId);
            db.AddInParameter(dbCommand, "CommentUserName", DbType.String, CommentUserName);
            db.AddInParameter(dbCommand, "CommentContent", DbType.String, CommentContent);
            db.AddInParameter(dbCommand, "CreatedTime", DbType.DateTime, CreatedTime);
            int cmdresult;
            object obj = db.ExecuteScalar(dbCommand);
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int AddComment(V_VideoComment model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_VideoComment(");
            strSql.Append("CourseListId,CommentUserId,CommentUserName,CommentContent,CreatedTime)");

            strSql.Append(" values (");
            strSql.Append("@CourseListId,@CommentUserId,@CommentUserName,@CommentContent,@CreatedTime)");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "CourseListId", DbType.Int32, model.CourseListId);
            db.AddInParameter(dbCommand, "CommentUserId", DbType.Int32, model.CommentUserId);
            db.AddInParameter(dbCommand, "CommentUserName", DbType.String, model.CommentUserName);
            db.AddInParameter(dbCommand, "CommentContent", DbType.String, model.CommentContent);
            db.AddInParameter(dbCommand, "CreatedTime", DbType.DateTime, model.CreatedTime);
            int resurt = db.ExecuteNonQuery(dbCommand);
            if (resurt == 1)
            {

                return 1;
            }
            else
            {

                return 0;
            }

        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(V_VideoComment model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_VideoComment set ");
            strSql.Append("ID=@ID,");
            strSql.Append("CourseListId=@CourseListId,");
            strSql.Append("CommentUserId=@CommentUserId,");
            strSql.Append("CommentUserName=@CommentUserName,");
            strSql.Append("CommentContent=@CommentContent,");
            strSql.Append("CreatedTime=@CreatedTime");
            strSql.Append(" where ID=@ID and CourseListId=@CourseListId and CommentUserId=@CommentUserId and CommentUserName=@CommentUserName and CommentContent=@CommentContent and CreatedTime=@CreatedTime ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
            db.AddInParameter(dbCommand, "CourseListId", DbType.Int32, model.CourseListId);
            db.AddInParameter(dbCommand, "CommentUserId", DbType.Int32, model.CommentUserId);
            db.AddInParameter(dbCommand, "CommentUserName", DbType.String, model.CommentUserName);
            db.AddInParameter(dbCommand, "CommentContent", DbType.String, model.CommentContent);
            db.AddInParameter(dbCommand, "CreatedTime", DbType.DateTime, model.CreatedTime);
            db.ExecuteNonQuery(dbCommand);

        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int ID, int CourseListId, int CommentUserId, string CommentUserName, string CommentContent, DateTime CreatedTime)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from V_VideoComment ");
            strSql.Append(" where ID=@ID and CourseListId=@CourseListId and CommentUserId=@CommentUserId and CommentUserName=@CommentUserName and CommentContent=@CommentContent and CreatedTime=@CreatedTime ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, ID);
            db.AddInParameter(dbCommand, "CourseListId", DbType.Int32, CourseListId);
            db.AddInParameter(dbCommand, "CommentUserId", DbType.Int32, CommentUserId);
            db.AddInParameter(dbCommand, "CommentUserName", DbType.String, CommentUserName);
            db.AddInParameter(dbCommand, "CommentContent", DbType.String, CommentContent);
            db.AddInParameter(dbCommand, "CreatedTime", DbType.DateTime, CreatedTime);
            db.ExecuteNonQuery(dbCommand);

        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,CourseListId,CommentUserId,CommentUserName,CommentContent,CreatedTime ");
            strSql.Append(" FROM V_VideoComment ");
            strSql.Append("Order by id desc");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 根据条件和学校id获取评论内容及其相关的内容信息。
        ///create by 桑慧娜
        /// </summary>
        public DataSet GetCommentAndOtherInfoList(string strWhere, int sid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select vc.ID as commentid,vc.CourseListId,CommentUserId,CommentUserName,CommentContent,CreatedTime,cl.trainClassID,VTC_Number,");
            strSql.Append(" VTC_name as TrainClassName,teacherID,VT_realname as TeacherName,courseID,VC_name as CourseName,VCL_startTime,VCL_EndTime,VCL_desc,VCL_startDate,VCL_EndDate");
            strSql.Append(" from V_VideoComment vc join V_courselist cl on vc.CourseListId=cl.ID");
            strSql.Append(" join V_trainClass tc on cl.trainClassID=tc.ID join V_teacher on V_teacher.ID=cl.teacherID ");
            strSql.Append(" join V_course on V_course.ID=cl.courseID where cl.SchoolID=" + sid.ToString());
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }
            strSql.Append(" order by CreatedTime desc");

            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        /// <summary>
        /// 根据评论id删除数据。
        ///create by 桑慧娜
        /// </summary>
        public int DeleteComment(int commentid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from V_VideoComment where ID=@cid ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "cid", DbType.Int32, commentid);
            return db.ExecuteNonQuery(dbCommand);
        }
        /// <summary>
        /// 获得数据列表（比DataSet效率高，推荐使用）
        /// </summary>
        public List<V_VideoComment> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,CourseListId,CommentUserId,CommentUserName,CommentContent,CreatedTime ");
            strSql.Append(" FROM V_VideoComment ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            List<V_VideoComment> list = new List<V_VideoComment>();
            Database db = VDataBase.CreateDatabase();
            using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(ReaderBind(dataReader));
                }
            }
            return list;
        }


        /// <summary>
        /// 对象实体绑定数据
        /// </summary>
        public V_VideoComment ReaderBind(IDataReader dataReader)
        {
            V_VideoComment model = new V_VideoComment();
            object ojb;
            ojb = dataReader["ID"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.ID = (int)ojb;
            }
            ojb = dataReader["CourseListId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CourseListId = (int)ojb;
            }
            ojb = dataReader["CommentUserId"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CommentUserId = (int)ojb;
            }
            model.CommentUserName = dataReader["CommentUserName"].ToString();
            model.CommentContent = dataReader["CommentContent"].ToString();
            ojb = dataReader["CreatedTime"];
            if (ojb != null && ojb != DBNull.Value)
            {
                model.CreatedTime = (DateTime)ojb;
            }
            return model;
        }

        /// <summary>
        /// 获取学生观看视频的报表，看一个视频被多少学生看过，并记录每个视频被学生观看查出这些学生的详细信息
        /// </summary>
        public DataSet GetUserViewVideoReport(string sqlwhere, int sid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select courselistid,UserViewNum, vc.trainClassID,VTC_name as TrainClassName,vc.teacherID,VTC_Number,");
            strSql.Append("VT_realname as TeacherName,vc.courseID,VC_name as CourseName, VCL_startDate ,VCL_startTime,VCL_EndTime from ");
            strSql.Append(" ( select vc.ID as courselistid,count(V_UserViewVideoLog.courselistID) as UserViewNum  from V_courselist vc left join V_UserViewVideoLog on vc.ID=V_UserViewVideoLog.courselistID  group by vc.ID ) as V_CL");
            strSql.Append(" join V_courselist  vc  on V_CL.courselistid=vc.ID join V_trainClass tc on vc.trainClassID=tc.ID  join V_teacher on V_teacher.ID=vc.teacherID  join V_course on V_course.ID=vc.courseID ");
            strSql.Append("where vc.SchoolID=" + sid.ToString());
            if (sqlwhere.Trim() != "")
            {
                strSql.Append(sqlwhere);
            }
            strSql.Append(" order by UserViewNum desc ");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        /// <summary>
        /// 观看具体某个视频的所有学生详细信息
        /// </summary>
        public DataSet GetVideoViewList(int courseid, int schoolId,int topCount)
        {
            StringBuilder strSql = new StringBuilder();
            if (topCount > 0)
            {
                strSql.Append("select top "+topCount);
            }
            else
            {
                strSql.Append("select ");
            }
            strSql.Append("  uv.ID,UserID,UserName,View_time,courselistID,uv.SchoolID ,VS_name,VU_loginname,VU_mobile, VU_email,IsAvailable from V_UserViewVideoLog as uv join V_school on V_school.ID=uv.SchoolID join V_user on V_user.ID=UserID  where  uv.SchoolID=" + schoolId.ToString() + " and  courselistID=" + courseid.ToString());
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());

        }

        /// <summary>
        /// 用存储过程实现分页观看具体某个视频的所有学生详细信息
        /// </summary>
        public DataSet GetVideoViewListByPro(VideoClassLibrary.Model.Pager pager)
        {
            //using (SqlConnection myConnection = new SqlConnection(connectionString))
            //  {
            // 调用分页
            //DataTable table = new DataTable();
            StringBuilder strsql = new StringBuilder();
            strsql.Append("GetPagingData");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand(strsql.ToString());
            db.AddInParameter(dbCommand, "tablename", DbType.String, pager.TableName);
            db.AddInParameter(dbCommand, "fieldlist", DbType.String, pager.SelectStr);
            db.AddInParameter(dbCommand, "orderfield", DbType.String, pager.Order);
            db.AddInParameter(dbCommand, "ordertype", DbType.Byte, pager.OrderType == true ? 0 : 1);
            db.AddInParameter(dbCommand, "keyfield", DbType.String, pager.KeyField);
            db.AddInParameter(dbCommand, "pageindex", DbType.String, pager.PageIndex);
            db.AddInParameter(dbCommand, "pagesize", DbType.Int32, pager.PageSize);
            db.AddInParameter(dbCommand, "strwhere", DbType.String, pager.WhereCondition);
            db.AddOutParameter(dbCommand, "RecordCount", DbType.Int32, pager.RecordCount);
            db.AddOutParameter(dbCommand, "PageCount", DbType.Int32, pager.PageCount);
            DataSet ds= db.ExecuteDataSet(dbCommand);
            pager.RecordCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "RecordCount"));
            
            pager.PageCount = Convert.ToInt32( db.GetParameterValue(dbCommand, "PageCount"));
         
            return ds;

        }

        /// <summary>
        /// 视频评论
        /// </summary>
        public DataSet GetListByCourseListId(int courseListid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top(3) a.ID,CourseListId,CommentUserId,CommentUserName,CommentContent,CreatedTime ");
            strSql.Append(" FROM V_VideoComment a inner join v_courselist b on a.courseListId=b.id");
            strSql.Append(" where a.courseListId=" + courseListid);
            strSql.Append("order by ID desc");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        #endregion  成员方法

    }
}
